package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.beans.*;
import com.constants.*;
import com.exceptions.ServiceUnavailableException;
import com.utility.*;

public class AdminDAO {

	
	public Person[] getAllSimpleMembers() throws ServiceUnavailableException,Exception
	{

		Person[] simpleMemberList =null;
		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			System.out.println("/n-------------------------/n");
			System.out.println("-------Simple Members--------/n");
			System.out.println("-----------------------------");
			
			
			String pstatement=SQLConstants.GETSIMPLEMEMBERSLIST;
			prestmt=con.prepareStatement(pstatement);
			System.out.println("Query framed: "+prestmt.toString());
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				simpleMemberList=new Person[size];
				
			
			
			int currRow=0;
		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode((int)rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance((double)rs.getDouble(13));			
				simpleMemberList[currRow]=person;
				currRow++;
			}
			}
			//Close all the open connections
			if(rs!=null)rs.close();
			if(prestmt!=null)prestmt.close();
			if(con!=null)con.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
			
	return simpleMemberList;
	}
	
	

	public Person[] getAllPremiumMembers() throws ServiceUnavailableException,Exception
	{
		Person[] premiumMemberList =null;
		
		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			System.out.println("/n-------------------------/n");
			System.out.println("-------Premium Members--------/n");
			System.out.println("-----------------------------");
			
			
			String pstatement=SQLConstants.GETPREMIUMMEMBERSLIST;
			prestmt=con.prepareStatement(pstatement);
			System.out.println("Query framed: "+prestmt.toString());
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				premiumMemberList=new Person[size];
			int currRow=0;		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode((int)rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit((int)rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance((double)rs.getDouble(13));
			
				premiumMemberList[currRow]=person;
				currRow++;
			}
			
			}
			if(rs!=null)rs.close();
			if(prestmt!=null)prestmt.close();
			if(con!=null)con.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
			
	return premiumMemberList;
	}
	
	
	public Person[] getAllMembersByState(String state) throws ServiceUnavailableException,Exception
	{
		Person[] AllMemberList=null;

		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members in a particular state--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETALLMEMBERSLISTBYSTATE;
			System.out.println(""+pStatement);
			prestmt = con.prepareStatement(pStatement);
			System.out.println(""+prestmt);


			prestmt.setString(1, "%" +state+ "%");

			
			System.out.println("\n Query to the persons depending on State is:" + prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				System.out.println("size:"+size);
				AllMemberList=new Person[size];
			int currRow=0;		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode(rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance(rs.getDouble(13));
			
				AllMemberList[currRow]=person;
				currRow++;
			}
			
			}
			if(rs!=null){rs.close();}
			if(prestmt!=null)prestmt.close();
			if (con!=null)con.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		
		
		return AllMemberList;
	}
	
	
	public Person[] getAllMembersByCity(String city) throws ServiceUnavailableException,Exception
	{
		Person[] AllMemberList=null;

		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members in a particular city--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETALLMEMBERSLISTBYCITY;
			System.out.println(""+pStatement);
			prestmt = con.prepareStatement(pStatement);
			System.out.println(""+prestmt);
			prestmt.setString(1, "%" + city + "%");
			
			System.out.println("\n Query to the persons depending on City is:" + prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				System.out.println("size:"+size);
				AllMemberList=new Person[size];
			int currRow=0;		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode(rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance(rs.getDouble(13));
			
				AllMemberList[currRow]=person;
				currRow++;
			}
			
			}
			if(con!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		
		
		return AllMemberList;
	}
	

	public Person[] getAllMembersByZip(int zip) throws ServiceUnavailableException,Exception
	{
		Person[] AllMemberList=null;

		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members in a particular zipcode--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETALLMEMBERSLISTBYZIP;
			System.out.println(""+pStatement);
			prestmt = con.prepareStatement(pStatement);
			System.out.println(""+prestmt);
			prestmt.setInt(1,zip);
			
			System.out.println("\n Query to the persons depending on Zip is:" + prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				System.out.println("size:"+size);
				AllMemberList=new Person[size];
			int currRow=0;		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode(rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance(rs.getDouble(13));
			
				AllMemberList[currRow]=person;
				currRow++;
			}
			
			}
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		
		
		return AllMemberList;
	}

	
	public Person[] getAllMembersByFname(String fname) throws ServiceUnavailableException,Exception
	{
		Person[] AllMemberList=null;

		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members in a particular First name--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETALLMEMBERSBYFNAME;
			System.out.println(""+pStatement);
			prestmt = con.prepareStatement(pStatement);
			System.out.println(""+prestmt);
			prestmt.setString(1,"%" +fname +"%");
			
			System.out.println("\n Query to the persons depending on First name is:" + prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				System.out.println("size:"+size);
				AllMemberList=new Person[size];
			int currRow=0;		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode(rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance(rs.getDouble(13));
			
				AllMemberList[currRow]=person;
				currRow++;
			}
			
			}
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		return AllMemberList;
	}


	
	public Person[] getAllMembersByLname(String lname) throws ServiceUnavailableException,Exception
	{
		Person[] AllMemberList=null;

		int size=0;
		
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members in a particular Last name--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETALLMEMBERSBYLNAME;
			System.out.println(""+pStatement);
			prestmt = con.prepareStatement(pStatement);
			System.out.println(""+prestmt);
			prestmt.setString(1,"%" +lname +"%");
			
			System.out.println("\n Query to the persons depending on Last name is:" + prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				System.out.println("size:"+size);
				AllMemberList=new Person[size];
			int currRow=0;		
			rs.beforeFirst();
			while(rs.next())
			{
				Person person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode(rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance(rs.getDouble(13));
			
				AllMemberList[currRow]=person;
				currRow++;
			}
			
			}//Close all the open connections
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		return AllMemberList;
	}
	

	
	public Person getAllPersonById(long memid) throws ServiceUnavailableException,Exception
	{
		
		Person person=null;
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			//connect to the database
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members based on membership number--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETALLMEMBERSBYID;
			System.out.println(""+pStatement);
			prestmt = con.prepareStatement(pStatement);
			System.out.println(""+prestmt);
			prestmt.setLong(1,memid);
			
			System.out.println("\n Query to the persons depending on Last name is:" + prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			rs.beforeFirst();
			while(rs.next())
			{
				person=new Person();
				person.setMemberShipNumber(rs.getLong(1));
				person.setCustomerType(rs.getString(2));
				person.setFirstName(rs.getString(3));
				person.setLastName(rs.getString(4));
				person.setAddress(rs.getString(5));
				person.setCity(rs.getString(6));
				person.setState(rs.getString(7));
				person.setZipCode(rs.getInt(8));
				person.setEmailId(rs.getString(9));
				person.setMovieLimit(rs.getInt(10));
				person.setIssuedMoviesList(rs.getString(11));
				person.setLastLoggedIn(rs.getString(12));
				person.setBalance(rs.getDouble(13));
		
			}
			
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null)con.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		return person;
	}

	
	public Movie getMoviesByName(String name) throws ServiceUnavailableException,Exception
	{
		
		Movie movie=null;
		Connection con = null;
		Connection con1=null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		String mypersonlist="";
		
		try {
			//connect to the database
			
			//con1 = DBConnection.getConnection();
			con1 = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All Members based on movie name--------/n");
			System.out.println("-----------------------------");
			
			
			String pStatement1=SQLConstants.ISSUEDMOVIESTOMEMBER;
			PreparedStatement prestmt1 = con1.prepareStatement(pStatement1);
			prestmt1.setString(1,"%" +name +"%");
			System.out.println("/nQuery framed: "+prestmt1.toString());
			ResultSet rs1 = prestmt1.executeQuery();
			rs1.last();
			int curr_row=rs1.getRow();
			System.out.println(""+curr_row);
			rs1.beforeFirst();
			
			try
			{
			while(rs1.next())
			{
				mypersonlist=mypersonlist+(rs1.getString(1))+",";
				
			
			}
		
			
			rs1.close();
			prestmt1.close();
			con1.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} 
			
		int length=mypersonlist.length();
		int lastindex=length-1;  
		char last=mypersonlist.charAt(lastindex);
		
		if (last==',')
		{
			mypersonlist= mypersonlist.substring(0, lastindex);
		}
		
			
			String pStatement = SQLConstants.GETMOVIEBYNAME;
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			prestmt=con.prepareStatement(pStatement);
			prestmt.setString(1, "%" +name +"%");
			System.out.println("/nQuery framed: "+prestmt.toString());
			
			rs=prestmt.executeQuery();
			
			rs.beforeFirst();
			while(rs.next())
			{
				movie=new Movie();
				movie.setMovieId(rs.getInt(1));
				movie.setMovieName(rs.getString(2));
				movie.setMovieBanner(rs.getString(3));
				movie.setReleaseDate(rs.getString(4));
				movie.setRent(rs.getDouble(5));
				movie.setCategoryid(rs.getInt(6));
				movie.setAvailableCopy(rs.getInt(7));
				movie.setCategoryname(rs.getString(8));
				movie.setIssuedtomembers(mypersonlist);
			}
			
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
			
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		return movie;
	}

	public Movie getMoviesByBanner(String banner) throws ServiceUnavailableException,Exception
	{
		
		Movie movie=null;
		Connection con = null;
		Connection con1=null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		PreparedStatement prestmt1 = null;
		ResultSet rs1 = null;
		
		String mypersonlist="";
		try {
			
			//con1 = DBConnection.getConnection();
			con1 = DBConnection.getConnection();

			String pStatement1=SQLConstants.ISSUEDMOVIESTOMEMBERBANNER;
			prestmt1 = con1.prepareStatement(pStatement1);
			prestmt1.setString(1,"%" +banner +"%");
			System.out.println("/nQuery framed: "+prestmt1.toString());
			rs1 = prestmt1.executeQuery();
			rs1.last();
			int curr_row=rs1.getRow();
			System.out.println(""+curr_row);
			rs1.beforeFirst();
			
			try
			{
			while(rs1.next())
			{
				mypersonlist=mypersonlist+(rs1.getString(1))+",";
			}
			
			rs1.close();
			prestmt1.close();
			con1.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} 
			
		int length=mypersonlist.length();
		int lastindex=length-1;  
		char last=mypersonlist.charAt(lastindex);
		
		if (last==',')
		{
			mypersonlist= mypersonlist.substring(0, lastindex);
		}
		
		
		
			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All movies based on movie banner--------/n");
			System.out.println("-----------------------------");
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			String pStatement = SQLConstants.GETMOVIEBYBANNER;
			
			prestmt=con.prepareStatement(pStatement);
			
			prestmt.setString(1,"%" +banner +"%");
			System.out.println("/nQuery framed: "+prestmt.toString());
			rs=prestmt.executeQuery();
			
			
			
			rs.beforeFirst();
			while(rs.next())
			{
				movie=new Movie();
				movie.setMovieId(rs.getInt(1));
				movie.setMovieName(rs.getString(2));
				movie.setMovieBanner(rs.getString(3));
				movie.setReleaseDate(rs.getString(4));
				movie.setRent(rs.getDouble(5));
				movie.setCategoryid(rs.getInt(6));
				movie.setAvailableCopy(rs.getInt(7));
				movie.setCategoryname(rs.getString(8));
				movie.setIssuedtomembers(mypersonlist);
			}
			
			
			System.out.println(""+movie.getIssuedtomembers());
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	
		return movie;
	}

	
	public Movie[] getMoviesReleaseDate(String date) throws ServiceUnavailableException,Exception
	{
		int size=0;
		Movie movies[]=null;
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		PreparedStatement prestmt1 = null;
		ResultSet rs1 = null;
		PreparedStatement prestmt2 = null;
		ResultSet rs2 = null;
		String member="";
		try {
			
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All movies based on release date--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETMOVIEBYRELEASEDATE;
			//System.out.println(""+pStatement);
			prestmt=con.prepareStatement(pStatement);
			prestmt.setString(1,"%" +date +"%");
			System.out.println("/nQuery framed: "+prestmt.toString());
			
			
			rs=prestmt.executeQuery();
			rs.last();
			size=rs.getRow();
			movies=new Movie[size];
			
		
		
		int currRow=0;
			rs.beforeFirst();
			while(rs.next())
			{
				member="";
				String pStatement1 = SQLConstants.GETMOVIEPERSONBYSTATUS;
				System.out.println(""+pStatement1);
				prestmt1=con.prepareStatement(pStatement1);
				prestmt1.setInt(1,rs.getInt(1));
				rs1=prestmt1.executeQuery();
				
				rs1.beforeFirst();
					while(rs1.next())
					{
						String pStatement2 = SQLConstants.GETPERSONNAME;
						System.out.println(""+pStatement2);
						prestmt2=con.prepareStatement(pStatement2);
						prestmt2.setLong(1,rs1.getLong(1));
						rs2=prestmt2.executeQuery();
						
						rs2.beforeFirst();
						while(rs2.next())
						{
						 member=member+rs2.getString(1)+',';
						}
					}
				Movie movie=new Movie();
				movie.setMovieId(rs.getInt(1));
				movie.setMovieName(rs.getString(2));
				movie.setMovieBanner(rs.getString(3));
				movie.setReleaseDate(rs.getString(4));
				movie.setRent(rs.getDouble(5));
				movie.setCategoryid(rs.getInt(6));
				movie.setAvailableCopy(rs.getInt(7));
				movie.setCategoryname(rs.getString(8));
				movie.setIssuedtomembers(member);
				
				movies[currRow]=movie;
				currRow++;
			}
			
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
			if(rs1!=null){rs1.close();}
			if(rs2!=null){rs2.close();}
			if(prestmt1!=null){prestmt1.close();}
			if(prestmt2!=null){prestmt2.close();}
		}
		
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
		return movies;
		
	}

	public Movie[] getMoviesByCategory(String category) throws ServiceUnavailableException,Exception
	{
		int size=0;
		Movie movies[]=null;
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			
			con = DBConnection.getConnection();
			
			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All movies based on category--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETMOVIEBYCATEGORY;
			
			prestmt=con.prepareStatement(pStatement);
			prestmt.setString(1,category);
			System.out.println("/nQuery framed: "+prestmt.toString());
			
			
			rs=prestmt.executeQuery();
			rs.last();
			size=rs.getRow();
			movies=new Movie[size];
			int currRow=0;
			rs.beforeFirst();
			while(rs.next())
			{	
				Movie movie=new Movie();
				movie.setMovieId(rs.getInt(1));
				movie.setMovieName(rs.getString(2));
				movie.setMovieBanner(rs.getString(3));
				movie.setReleaseDate(rs.getString(4));
				movie.setRent(rs.getDouble(5));
				movie.setCategoryid(rs.getInt(6));
				movie.setAvailableCopy(rs.getInt(7));
				movie.setCategoryname(rs.getString(8));				
				movies[currRow]=movie;
				currRow++;
			}
			if(rs!=null)
			{
			rs.close();
			}
			if(prestmt!=null)
			{prestmt.close();}
			if(con!=null){con.close();}
		}
		
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
		return movies;
		
	}
	public Movie[] getMoviesByRent(double rent) throws ServiceUnavailableException,Exception
	{
		int size=0;
		Movie movies[]=null;
		Connection con = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		PreparedStatement prestmt1 = null;
		ResultSet rs1 = null;
		ResultSet rs2=null;
		PreparedStatement prestmt2=null;
		String member="";
		
		try {
			
			//con = DBConnection.getConnection();
			con = DBConnection.getConnection();

			
			System.out.println("/n-------------------------/n");
			System.out.println("-------All movies based on movie rent--------/n");
			System.out.println("-----------------------------");
			
			String pStatement = SQLConstants.GETMOVIEBYRENT;
			System.out.println(""+pStatement);
			prestmt=con.prepareStatement(pStatement);
			prestmt.setDouble(1,rent);
			System.out.println("/nQuery framed: "+prestmt.toString());
			
			
			rs=prestmt.executeQuery();
			rs.last();
			size=rs.getRow();
			movies=new Movie[size];
			
		
		
		int currRow=0;
			rs.beforeFirst();
			while(rs.next())
			{
				member="";
				String pStatement1 = SQLConstants.GETMOVIEPERSONBYSTATUS;
				System.out.println(""+pStatement1);
				prestmt1=con.prepareStatement(pStatement1);
				prestmt1.setInt(1,rs.getInt(1));
				rs1=prestmt1.executeQuery();
				
				rs1.beforeFirst();
					while(rs1.next())
					{
						
						String pStatement2 = SQLConstants.GETPERSONNAME;
						//System.out.println(""+pStatement2);
						prestmt2=con.prepareStatement(pStatement2);
						prestmt2.setLong(1,rs1.getLong(1));
						rs2=prestmt2.executeQuery();
						
						rs2.beforeFirst();
						while(rs2.next())
						{
						 member=member+rs2.getString(1)+',';
						}
					}
					
				Movie movie=new Movie();
				movie.setMovieId(rs.getInt(1));
				movie.setMovieName(rs.getString(2));
				movie.setMovieBanner(rs.getString(3));
				movie.setReleaseDate(rs.getString(4));
				movie.setRent(rs.getDouble(5));
				movie.setCategoryid(rs.getInt(6));
				movie.setAvailableCopy(rs.getInt(7));
				movie.setCategoryname(rs.getString(8));
				movie.setIssuedtomembers(member);
				movies[currRow]=movie;
				currRow++;
			}
			
			
			
			if(rs!=null){rs.close();}
			if(prestmt!=null){prestmt.close();}
			if(con!=null){con.close();}
			if(rs1!=null){rs1.close();}
			if(rs2!=null){rs2.close();}
			if(prestmt1!=null){prestmt1.close();}
			if(prestmt2!=null){prestmt2.close();}
		}
		
		catch(SQLException e){
			e.printStackTrace();
			throw new ServiceUnavailableException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServiceUnavailableException();
		}		
		finally{
			try{
				rs.close();
			}catch(Exception e){
				
			}
			try{
				prestmt.close();
			}catch(Exception e){
				
			}
			try{
				con.close();
			}catch(Exception e){
				
			}
			
		}
		return movies;
		
	}
	
}
